# coding=utf-8

import MySQLdb
import datetime
import time

import sys

reload(sys)
sys.setdefaultencoding('utf-8')

Host = "118.31.32.197"
Port = 3306
User = "gmj"
Passwd = "123456"
Db = "worldcup"

conn = MySQLdb.connect(host=Host, port=Port, user=User, passwd=Passwd, db=Db, charset="utf8")
cursor = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)

# 统计30天内查看数量
# now = datetime.datetime.now()
#
# for i in range(30):
# 	that_day = now - datetime.timedelta(i)
# 	start = datetime.datetime(that_day.year, that_day.month, that_day.day, 0, 0, 0)
# 	end = datetime.datetime(that_day.year, that_day.month, that_day.day, 23, 59, 59)
# 	start_timestamp = int(time.mktime(start.timetuple()))
# 	end_timestamp = int(time.mktime(end.timetuple()))
#
# 	timeArray = time.localtime(start_timestamp)
# 	otherStyleTime = time.strftime("%Y-%m-%d", timeArray)
#
# 	sql = "select d.wxname , d.view_num  from (select c.wxname, count(c.openid) as view_num from (select distinct a.openid,a.rec_openid, b.wxname from userModel_travelevent a ,userModel_user b where  a.openid = b.openid AND  a.event = 2 and created_at >= {} and created_at <= {} ) c  group by c.openid order by view_num desc) d where d.view_num >= 6".format(start_timestamp, end_timestamp)
# 	cursor.execute(sql)
# 	rows = cursor.fetchall()
# 	if len(rows) != 0:
# 		print otherStyleTime
# 	for row in rows:
# 		print row["wxname"] + ",",
# 	if len(rows) != 0:
# 		print ""

# 统计订单
cursor.execute("select * from userModel_orderhead where order_status = 20")
order_heads = cursor.fetchall()
print "{:<25}      {:<15} {:<30} {:<2} {:<3}".format("订单号", "微信名", "商品名", "数量","总价格")
for order_head in order_heads:
	cursor.execute("select * from userModel_orderdetail where order_code = " + str(order_head["order_code"]))
	order_details = cursor.fetchall()
	for order_detail in order_details:
		order_code = str(order_detail["order_code"])
		cursor.execute("select * from userModel_user where openid = '" + order_head["openid"] + "'")
		user = cursor.fetchall()[0]
		print "{:<25} {:<15} {:<10} {:<2} {:<3}".format(order_code, user["wxname"], order_detail["sku_name"], str(order_detail["qty"]), str(order_detail["qty"] * order_detail["price"]))



